#!/usr/bin/env python
# encoding: utf-8

# 导入:
from sqlalchemy import (Table, MetaData, Column, Integer,Boolean,
                        Float, String, DateTime, ForeignKey, create_engine)
from sqlalchemy.orm import sessionmaker,relationship,backref
from sqlalchemy.orm import mapper
from sqlalchemy import func
from sqlalchemy.ext.declarative import declarative_base

from datetime import datetime
import os

# 创建对象的基类:
Base = declarative_base()

class Node(Base):
    __tablename__ = 'node'
    id = Column(Integer, primary_key=True)
    name     = Column(String(30))
    cid      = Column(String(30),unique=True)
    number   = Column(String(30))
    version  = Column(String(10))
    lora_addr= Column(String(20))
    position = Column(String(80))
    status   = Column(String(30))
    alive    = Column(Boolean)
    def __init__(self,
                 name='',
                 cid='',
                 number='',
                 version='',
                 lora_addr='',
                 position='',
                 status='',
                 alive=False):
        self.name     = name
        self.cid      = cid
        self.number   = number
        self.version   = version
        self.lora_addr = lora_addr
        self.position = position
        self.status   = status
        self.alive    = alive

class NodeRecord(Base):
    __tablename__ = 'node_record'
    id        = Column(Integer, primary_key=True)
    node_id   = Column(Integer,ForeignKey('node.id'))
    node      = relationship('Node', backref=backref('noderecord', lazy='dynamic'))
    operator  = Column(String(30))
    operation = Column(String(10))
    time      = Column(String(30))
    def __init__(self, node='', operator='', operation=''):
        self.node      = node
        self.operator  = operator
        self.operation = operation
        self.time      = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

class NodeConnectRecord(Base):
    __tablename__ = 'node_connect_record'
    id        = Column(Integer, primary_key=True)
    node_id   = Column(Integer,ForeignKey('node.id'))
    node      = relationship('Node', backref=backref('nodeconnectrecord', lazy='dynamic'))
    online    = Column(String(30))
    time      = Column(String(30))
    def __init__(self, node='', online=''):
        self.node   = node
        self.online = online
        self.time   = datetime.now().strftime('%Y-%m-%d %H:%M:%S')

class Sql:
    def __init__(self):
        self.__sqlite_path = './nodes.db'
        # 创建数据库
        engine = create_engine('sqlite:///%s'%self.__sqlite_path)
        # 初始化数据库连接:
        if not os.path.exists(self.__sqlite_path):
            Base.metadata.create_all(engine)
        DBSession = sessionmaker(bind=engine)
        self.session = DBSession()
        # 添加测试数据
        # self.addTestData()
        # self.node_filter(cid='4e010001')
        # print(self.get_nodes())

    def get_nodes(self):
        res = self.session.query(Node).all()
        return res

    def node_filter(self, cid=''):
        n = self.session.query(Node).filter(func.lower(Node.cid)==func.lower(cid)).first()
        node = {
                    "name":n.name,
                    "cid":n.cid,
                    "number":n.number,
                    "lora_addr":n.lora_addr,
                    "position":n.position,
                    "version":n.version,
                    "status":n.status,
                    "alive":n.alive,
                  }
        return node

    def modify_node_alive(self, cid='', alive=False):
        c = self.session.query(Node).filter(func.lower(Node.cid)==func.lower(cid)).first()
        if c:
            c.alive = alive
            self.session.commit()
            print("修改 Node.alive = %s"%alive)

    def addTestData(self):
        # 添加数据
        node1 = Node(
                 name='节点1号',
                 cid='4e010001',
                 number='0001',
                 version='1.1.1',
                 lora_addr='ff0112',
                 position='1号地',
                 status='阀门打开',
                 alive=False)
        node2 = Node(
                 name='节点2号',
                 cid='4e010002',
                 number='0002',
                 version='1.1.1',
                 lora_addr='ff0212',
                 position='2号地',
                 status='阀门关闭',
                 alive=False)
        node3 = Node(
                 name='节点3号',
                 cid='4e010003',
                 number='0003',
                 version='1.1.1',
                 lora_addr='ff0312',
                 position='3号地',
                 status='阀门打开',
                 alive=False)
        self.session.add(node1)
        self.session.add(node2)
        self.session.add(node3)
        node_record = NodeRecord(node1, '熊','打开')
        self.session.add(node_record)
        self.session.commit()

if __name__ == '__main__':
    sql = Sql()

